import sys

import requests
import time
import pymysql
import requests
from tqdm import tqdm

DB_CONFIG = {
    'host': 'rm-2ze6q103m284te7f7eo.mysql.rds.aliyuncs.com',  # 数据库主机地址
    'port': 3306,  # 数据库端口
    'user': "root",  # 数据库用户名
    'password': 'Ds2024@()833429',  # 数据库密码
    'database': "qrqd"  # 数据库名称
}
mydb1 = pymysql.connect(**DB_CONFIG)
cursor = mydb1.cursor()
BAIDU_AK = "eTg5SBFOio6J0N2qciBKbNg2NDhfxgY4"  # 你的 key
GEOCODE_URL = "https://api.map.baidu.com/geocoding/v3/"


def baidu_geocode(id, address: str, timeout=5):
    """
    百度地理编码 V3.0：地址 → 经纬度（百度坐标系 bd09）
    :param address:  要解析的详细地址
    :param timeout:  请求超时秒数
    :return:         dict {"lng": 116.306, "lat": 40.047} 或 None
    """
    params = {
        "address": address,
        "output": "json",
        "ak": BAIDU_AK,
        "callback": ""  # 空值即可，不要 JSONP
    }
    resp = requests.get(GEOCODE_URL, params=params, timeout=timeout)
    resp.raise_for_status()
    data = resp.json()
    if data.get("status") == 0:
        loc = data["result"]["location"]
        lng, lat = loc["lng"], loc["lat"]
        return lng, lat



query_sql = 'select id,region,address from customers where lat is null'
update_sql = 'update customers set lng=%s,lat=%s where id=%s'
cursor.execute(query_sql)
results = cursor.fetchall()
for result in tqdm(results):
    id = result[0]
    region = result[1] + result[2]
    lng, lat = baidu_geocode(id, region)
    cursor.execute(update_sql, (lng, lat, id))
    time.sleep(0.03)
    mydb1.commit()
